package wef.utils.db;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;

public class JdbcUtil {

    public static void close(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(Statement sta) {
        if (sta != null) {
            try {
                sta.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void close(ResultSet rs, Statement sta, Connection conn) {
        close(rs);
        close(sta, conn);
    }

    public static void close(Statement sta, Connection conn) {
        close(sta);
        close(conn);
    }

    public static void close(ResultSet set, Statement state) {
        close(set);
        close(state);
    }

    public static void rollback(Connection conn) {
        if (conn != null) {
            try {
                conn.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void cancleTransaction(Connection conn) {
        if (conn != null) {
            try {
                conn.setAutoCommit(true);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static String generateInsertSql(Map<String, Object> map, String tableName, String excludeFields) {
        StringBuilder sb = new StringBuilder();
        return sb.toString();
    }

    public static String generateUpdateSql(Map<String, Object> map, String tableName, String excludeFields,String whereStr) {

        StringBuilder sb = new StringBuilder();
        return sb.toString();
    }

    /**
     * 通过反射机制生成通用的插入语句
     *
     * @param clazz
     * @param tableName
     * @param excludeFields
     * @param <T>
     * @return
     */
    public static <T> String generateInsertSql(Class<T> clazz, String tableName, String excludeFields) {
        String[] arr = null;
        if (excludeFields != null && excludeFields.length() > 0) {
            arr = excludeFields.split(",");
        }

        StringBuffer bufferFront = new StringBuffer();
        StringBuffer bufferLast = new StringBuffer();

        bufferFront.append("insert into " + tableName + "(");
        bufferLast.append(" values(");
        // 通过class得到所有的属性不受访问控制符空值
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            String fieldName = field.getName();
            boolean isinarr = isInArray(fieldName, arr);
            if (!isinarr) {
                bufferFront.append(fieldName + ",");
                bufferLast.append("#{" + fieldName + "},");
            }
        }
        bufferFront.delete(bufferFront.length() - 1, bufferFront.length());
        bufferLast.delete(bufferLast.length() - 1, bufferLast.length());
        bufferFront.append(")");
        bufferLast.append(")");
        bufferFront.append(bufferLast);

        return bufferFront.toString();
    }

    /**
     * 通过反射机制生成通用的更新语句
     *
     * @param clazz
     * @param excludeFields 不更新的列，不会出现在set语句中
     * @return
     * @throws Exception
     */
    public static String generateUpdateSql(Class<?> clazz, String tableName, String excludeFields, String whereStr)
            throws Exception {
        String[] arr = null;
        if (excludeFields != null && excludeFields.length() > 0) {
            arr = excludeFields.split(",");
        }
        if (whereStr == null || whereStr.length() == 0)
            throw new Exception("更新语句必须指定where条件");

        StringBuffer bufferFront = new StringBuffer();
        StringBuffer bufferLast = new StringBuffer();
        bufferFront.append("update " + tableName + " set ");
        bufferLast.append(" where " + whereStr);
        // 通过class得到所有的属性不受访问控制符空值
        Field[] fields = clazz.getDeclaredFields();
        for (Field field : fields) {
            String fieldName = field.getName();
            boolean isinarr = isInArray(fieldName, arr);
            if (!isinarr)
                bufferFront.append(fieldName + "=#{" + fieldName + "},");
        }
        bufferFront.delete(bufferFront.length() - 1, bufferFront.length());
        bufferFront.append(bufferLast);
        return bufferFront.toString();
    }

    /**
     * 判断字符串是否在数组中，不区分大小写
     *
     * @param field
     * @param arr
     * @return
     */
    private static boolean isInArray(String field, String[] arr) {
        if (field == null || field.equals(""))
            return false;
        if (arr == null || arr.length == 0)
            return false;
        for (String item : arr) {
            if (item.equalsIgnoreCase(field))
                return true;
        }
        return false;
    }

}
